package com.asiainfo.iam.service;


import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

@Service
public class DMDatabaseOperateService {

    private static final Logger log  = LoggerFactory.getLogger(DMDatabaseConnService.class);


    /**
     * 向DM数据库定表中添加密列
     * @param databaseName 数据库名称
     * @param tableName 表名称
     * @param columnName 字段名称
     * @param columnType 字段类型
     * @param connection 数据库连接
     */
    public void executeAlterTable(String databaseName, String tableName, String columnName, String columnType, Connection connection) {

        // 创建 Statement 对象
        try (Statement stmt = connection.createStatement()) {
            String alterTableSQL = "ALTER TABLE " + databaseName + "." + tableName + " ADD " + columnName + " " + columnType;

//            String alterTableSQL = "ALTER TABLE " + databaseName + "." + tableName + " ADD " + columnName + " " + columnType + " encrypt with koal_SM4_mod1 manual";
            // 执行 SQL 语句
            log.info("开始执行 添加字段sql语句: {}", alterTableSQL);
            stmt.executeUpdate(alterTableSQL);
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        } catch (Exception e) {
            log.error("发生了 Exception 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        }
    }

    /**
     * 向DM数据库定表检查字段是否存在
     * @param databaseName 数据库名称
     * @param tableName 表名称
     * @param columnName 字段名称
     * @param connection 数据库连接
     */
    public boolean checkColumnExists(String databaseName, String tableName, String columnName, Connection connection) throws SQLException{
        // 获取数据库元数据
        DatabaseMetaData metaData = connection.getMetaData();
        // 创建 ResultSet 对象
        try (ResultSet resultSet = metaData.getColumns(null, databaseName.toUpperCase(), tableName, columnName)) {
            // 检查字段是否存在
            return resultSet.next();
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询指定数据库和表中的记录总数。
     * @param databaseName 数据库名称
     * @param tableName 表名称
     * @param connection 数据库连接
     * @param conditionTemp 动态 WHERE 条件字符串
     * @return 返回符合条件的记录总数
     */
    public long queryTableRowSum(String databaseName, String tableName, Connection connection, String conditionTemp) {
        // // 构建SQL查询语句，用于统计满足条件的行数
        String sqlQuery = "SELECT count(*) FROM " + databaseName.toUpperCase() + "." + tableName + " WHERE 1=1" + conditionTemp;
        // 行数
        long rowCount = 0L;
        log.info("开始执行 sqlquerySQL 语句: {}", sqlQuery);

        try (PreparedStatement pstmt = connection.prepareStatement(sqlQuery);
             ResultSet rs = pstmt.executeQuery()) {
            // 获取结果集
            if (rs.next()) {
                rowCount = rs.getLong(1);
            }
        } catch (SQLException var42) {
            log.error("发生了 SQLException 异常: {}", var42.getMessage());
            throw new RuntimeException(var42);
        }
        return rowCount;
    }

    /**
     * 获取指定数据库和表主键列名
     * @param databaseName 数据库名称
     * @param tableName 表名称
     * @param connection 数据库连接
     * @return 返回主键列名列表
     */
    public List<String> getPrimaryKeyOfDamengTable(String databaseName, String tableName, Connection connection) throws SQLException {
        List<String> findNameList = new LinkedList<>();
        boolean havePK = false;

        DatabaseMetaData metaData = null;
        try {
            metaData = connection.getMetaData();
            try (ResultSet resultSet = metaData.getPrimaryKeys(null, databaseName.toUpperCase(), tableName)) {
                while (resultSet.next()) {
                    // 判断是否有主键
                    havePK = true;
                    // 获取主键列名
                    String primaryKeyColumnName = resultSet.getString("COLUMN_NAME");
                    // 将主键列名添加到列表中
                    findNameList.add(primaryKeyColumnName);
                }
            }
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        }
        if (!havePK) {
            throw new RuntimeException(" " + databaseName + " 下的 " + tableName + " 表没有主键，请先为此表创建主键");
        }
        return findNameList;
    }

    /**
     * 更新表字段(verifyy(验签),b64SignedData,b64Cert)
     * @param databaseName 数据库名称
     * @param tableName 表名称
     * @param connection 数据库连接
     * @param findNameList 查询字段列表
     * @param valueSQL 字段值列表
     * @param message 验证信息
     * @return 返回更新的行数
     */
    public long updateB64SignedDataColumn(String databaseName,
                                  String tableName,
                                  Connection connection,
                                  List<String> findNameList,
                                  List<String> valueSQL,
                                  String message) {

        // 创建 PreparedStatement 对象
        PreparedStatement preparedStatement = null;
        try {
            String sql = "UPDATE " + databaseName + "." + tableName + " SET b64SignedData = '" + message + "' WHERE 1 = 1";
            StringBuilder conditionString = new StringBuilder();
            log.info("开始执行加签SQL 语句: {}", sql);
            for(int i = 0; i < findNameList.size(); ++i) {
                conditionString.append(" and ").append((String)findNameList.get(i)).append("='").append((String)valueSQL.get(i)).append("'");
            }
            // 创建 SQL 语句
            preparedStatement = connection.prepareStatement(sql + conditionString);
            log.info("开始执行多列加签SQL 语句: {}", sql+ conditionString);

            //返回执行的行数
            long rowsUpdated = preparedStatement.executeUpdate();
            //更新时间（判断是签名时间还是验证时间）
            this.updateTableColumnSignedDataUpdataTime(databaseName, tableName, connection, findNameList, valueSQL);
            //返回更新行数
            return rowsUpdated;
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            if (preparedStatement != null) {
                // 在 try-with-resources 中无需手动关闭 preparedStatement，已由自动资源管理处理
                //preparedStatement.close();
            }
        }
    }

    public long updateB64CertColumn(String databaseName,
                                          String tableName,
                                          Connection connection,
                                          List<String> findNameList,
                                          List<String> valueSQL,
                                          String message) {

        // 创建 PreparedStatement 对象
        PreparedStatement preparedStatement = null;
        try {
            String sql = "UPDATE " + databaseName + "." + tableName + " SET b64Cert = '" + message + "' WHERE 1 = 1";
            StringBuilder conditionString = new StringBuilder();
            log.info("开始执行加签SQL 语句: {}", sql);
            for(int i = 0; i < findNameList.size(); ++i) {
                conditionString.append(" and ").append((String)findNameList.get(i)).append("='").append((String)valueSQL.get(i)).append("'");
            }
            // 创建 SQL 语句
            preparedStatement = connection.prepareStatement(sql + conditionString);
            log.info("开始执行多列加签SQL 语句: {}", sql+ conditionString);

            //返回执行的行数
            long rowsUpdated = preparedStatement.executeUpdate();
            //更新时间（判断是签名时间还是验证时间）
            this.updateTableColumnSignedDataUpdataTime(databaseName, tableName, connection, findNameList, valueSQL);
            //返回更新行数
            return rowsUpdated;
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            if (preparedStatement != null) {
                // 在 try-with-resources 中无需手动关闭 preparedStatement，已由自动资源管理处理
                //preparedStatement.close();
            }
        }
    }
    public long updateTableColumnYanQian(String databaseName, String tableName, Connection connection, List<String> findNameList, List<String> valueSQL, String message) throws SQLException {
        PreparedStatement preparedStatement = null;

        try {
            String sql = "UPDATE " + databaseName + "." + tableName + " SET verifys = '" + message + "' WHERE 1 = 1";
            StringBuilder conditionString = new StringBuilder();
            log.info("开始执行 验签SQL 语句: {}", sql);

            for(int i = 0; i < findNameList.size(); ++i) {
                conditionString.append(" and ").append((String)findNameList.get(i)).append("='").append((String)valueSQL.get(i)).append("'");
            }

            preparedStatement = connection.prepareStatement(sql + conditionString);
            long rowsUpdated = (long)preparedStatement.executeUpdate();
            this.updateTableColumnVerifyyUpDataTime(databaseName, tableName, connection, findNameList, valueSQL);
            long var12 = rowsUpdated;
            return var12;
        } catch (SQLException var17) {
            System.out.println();
            log.error((String)"发生了 SQLException 异常: {}", (Object)var17.getMessage());
            throw new RuntimeException(var17);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }

        }
    }


    /**
     * 添加更新时间(签名时间和验签时间)
     */
    public int updateTableColumnSignedDataUpdataTime(String databaseName,
                          String tableName,
                          Connection connection,
                          List<String> findNameList,
                          List<String> valueSQL)throws SQLException{
        // 创建 PreparedStatement 对象
        PreparedStatement preparedStatement = null;
        // 创建时间
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());
        try {
            String sql = "UPDATE " + databaseName + "." + tableName + " SET signedDataUpdataTime = '" + timestamp + "' WHERE 1 = 1";
            StringBuilder conditionString = new StringBuilder();
            log.info("开始执行 添加更新时间SQL 语句: {}", sql);
            int i;
            for(i = 0; i < findNameList.size(); ++i) {
                conditionString.append(" and ").append((String)findNameList.get(i)).append("='").append((String)valueSQL.get(i)).append("'");
            }


            preparedStatement = connection.prepareStatement(sql + conditionString);
            log.info("开始执行 添加多列更新时间SQL 语句: {}", sql + conditionString);

            i = preparedStatement.executeUpdate();

            return i;
        } catch (SQLException e) {
            log.error("发生了 SQLException 异常: {}", e.getMessage());
            throw new RuntimeException(e);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }
        }
    }
    public int updateTableColumnVerifyyUpDataTime(String databaseName, String tableName, Connection connection, List<String> findNameList, List<String> valueSQL) throws SQLException {
        PreparedStatement preparedStatement = null;
        Timestamp timestamp = new Timestamp(System.currentTimeMillis());

        try {
            String sql = "UPDATE " + databaseName + "." + tableName + " SET verifyUpDataTime = '" + timestamp + "' WHERE 1 = 1";
            StringBuilder conditionString = new StringBuilder();
            log.info("开始执行 添加更新时间SQL 语句: {}", sql);

            int i;
            for(i = 0; i < findNameList.size(); ++i) {
                conditionString.append(" and ").append((String)findNameList.get(i)).append("='").append((String)valueSQL.get(i)).append("'");
            }

            preparedStatement = connection.prepareStatement(sql + conditionString);
            log.info("开始执行 添加多列更新时间SQL 语句: {}", sql + conditionString);
            i = preparedStatement.executeUpdate();
            return i;
        } catch (SQLException var14) {
            System.out.println();
            log.error((String)"发生了 SQLException 异常: {}", (Object)var14.getMessage());
            throw new RuntimeException(var14);
        } finally {
            if (preparedStatement != null) {
                preparedStatement.close();
            }

        }
    }

}
